/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Project.DataLayer;

import DataModel.*;
import CommonData.CommonDataManager;
import Organization.OrganizationManager;
import Utilities.*;
import java.util.*;
import java.sql.ResultSet;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import java.text.SimpleDateFormat;

/**
 *
 * @author Carlos
 */
public class ProjectDataManager {

    // <editor-fold defaultstate="collapsed" desc="Singleton">
    private static ProjectDataManager _instance = new ProjectDataManager();

    private ProjectDataManager()
    {
    }

    public static ProjectDataManager GetInstance()
    {
        return _instance;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Public Methods">

    public Project GetProject(int projectId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = "ObtenerProyecto";
        SQLParameter[] parameters = { new SQLParameter("idProyecto", projectId) };
        Project result = null;
        try
        {
            ResultSet reader = accesor.ExecuteInputSP(spName, parameters);
            if(reader.next())
            {
                String projectName = reader.getString("NombreProyecto");
                Date creationDate = reader.getDate("FechaCreacion");
                Date startDate = reader.getDate("FechaInicio");
                Date endDate = reader.getDate("FechaFinalizacion");
                String account = reader.getString("CuentaBancaria");
                double initialBudget = reader.getDouble("PresupuestoInicialDolares");
                double currentBudget = reader.getDouble("PresupuestoActualDolares");
                String projectDescription = reader.getString("DescripcionProyecto");
                String projectObjectives = reader.getString("ObjetivosProyecto");
                
                String projectType = reader.getString("TipoProyecto");
                Enums.ProjectType type = EnumConverter.GetProjectTypeFromString(projectType);
                
                String projectStatus = reader.getString("NombreEstadoProyecto");
                Enums.ProjectState status = EnumConverter.GetProjectStateFromString(projectStatus);

                int countryId = reader.getInt("IdPais");
                String countryName = reader.getString("NombrePais");
                Country country = new Country(countryId, countryName);

                int organizationId = reader.getInt("IdOrganizacion");
                String organizationName = reader.getString("NombreOrganizacion");
                OrganizationSummary organization = new OrganizationSummary(organizationId, organizationName);

                int risponsibleId = reader.getInt("IdUsuario");
                String risponsibleName = reader.getString("NombrePersona");
                String risponsibleLastName1 = reader.getString("PrimerApellido");
                String risponsibleLastName2 = reader.getString("SegundoApellido");
                PersonSummary risponsible = new PersonSummary(risponsibleId, risponsibleName, risponsibleLastName1, risponsibleLastName2);

                Person[] contacts = new Person[0];//ToDo: Extraer esta info en caso de ser necesario
                OrganizationRelation[] involvedOrganizations = new OrganizationRelation[0];

                result = new Project(projectId, projectName, creationDate, startDate, endDate, account, initialBudget, currentBudget, country, organization, projectDescription, projectObjectives, status, type, contacts, risponsible, involvedOrganizations);

            }
            reader.close();
        }
        catch(Exception ex) { }
        return result;
    }

    public void AssingProjectOfficer(int projectId, int officerId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = "AsignarOficialProyecto";
        SQLParameter[] parameters =
            {new SQLParameter("idProyecto", projectId),
            new SQLParameter("idUsuario", officerId)};
        try
        {
            accesor.ExecuteOutputSP(spName, parameters);
        }
        catch(Exception ex)
        {
        }
    }

    // <editor-fold defaultstate="collapsed" desc="Get Projects">
    public ProjectSummary[] GetProjects(Enums.ProjectState status, Enums.ProjectType type, int pageNumber, int projectsCount)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type)),
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_STATUS, EnumConverter.GetEnumName(status)),
            new SQLParameter(Constants.SP_PARAM_START_ROW, DataAccesor.GetStartRowForSP(pageNumber, projectsCount)),
            new SQLParameter(Constants.SP_PARAM_END_ROW, DataAccesor.GetEndRowForSP(pageNumber, projectsCount))
        };
        return getProjectSummaries(spName, parameters);
    }

    public ProjectSummary[] GetProjectsByType(Enums.ProjectType type, int pageNumber, int projectsCount)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS_BYTYPE;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type)),
            new SQLParameter(Constants.SP_PARAM_START_ROW, DataAccesor.GetStartRowForSP(pageNumber, projectsCount)),
            new SQLParameter(Constants.SP_PARAM_END_ROW, DataAccesor.GetEndRowForSP(pageNumber, projectsCount))
        };
        return getProjectSummaries(spName, parameters);
    }

    public ProjectSummary[] GetProjectsByCountry(int countryId, Enums.ProjectType type, int pageNumber, int projectsCount)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS_BYCOUNTRY;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_COUNTRY_ID, countryId),
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type)),
            new SQLParameter(Constants.SP_PARAM_START_ROW, DataAccesor.GetStartRowForSP(pageNumber, projectsCount)),
            new SQLParameter(Constants.SP_PARAM_END_ROW, DataAccesor.GetEndRowForSP(pageNumber, projectsCount))
        };
        return getProjectSummaries(spName, parameters);
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Count Projects">
    public int GetProjectsCount(Enums.ProjectType type, Enums.ProjectState status)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS_COUNT;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type)),
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_STATUS, EnumConverter.GetEnumName(status))
        };
        return getProjectsCount(spName, parameters);
    }

    public int GetProjectsCount(Enums.ProjectType type)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS_BYTYPE_COUNT;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type))
        };
        return getProjectsCount(spName, parameters);
    }

    public int GetProjectsCount(Enums.ProjectType type, int countryId)
    {
        String spName = Constants.SP_NAME_GET_PROJECTS_BYCOUNTRY_COUNT;
        SQLParameter[] parameters = new SQLParameter[]
        {
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_TYPE, EnumConverter.GetEnumName(type)),
            new SQLParameter(Constants.SP_PARAM_GET_PROJECTS_COUNTRY_ID, countryId)
        };
        return getProjectsCount(spName, parameters);
    }
    // </editor-fold>

    public Project[] GetIncompleteRequests(Organization organization, int responsibleId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = Constants.SP_NAME_GET_INCOMP_REQ;
        SQLParameter[] parameters =
                {new SQLParameter(Constants.SP_PARAM_GET_INCOMP_REQ_ORG_ID, organization.getId()),
                new SQLParameter(Constants.SP_PARAM_GET_INCOMP_REQ_USER_ID, responsibleId)};
        try
        {
            ResultSet result = accesor.ExecuteInputSP(spName, parameters);
            StringBuilder sb = new StringBuilder();
            while(result.next())
                sb.append(result.getString(1));
            result.close();
            DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
            org.xml.sax.InputSource inStream = new org.xml.sax.InputSource();
            inStream.setCharacterStream(new java.io.StringReader(Constants.XML_START + sb.toString() + Constants.XML_END));
            Document xmlDoc = docBuilder.parse(inStream);
            // Creamos la organización
            NodeList nodeList = xmlDoc.getElementsByTagName(Constants.XML_PROJ_HEADER_NODE);
            LinkedList<Project> projList = new LinkedList<Project>();
            for(int xmlIndex = 0; xmlIndex < nodeList.getLength(); xmlIndex++)
            {
                Node orgNode = nodeList.item(xmlIndex);
                NamedNodeMap attributes = orgNode.getAttributes();
                int countryId = Integer.parseInt(attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_COUNTRY_ID).getNodeValue());
                int projectId = Integer.parseInt(attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_ID).getNodeValue());
                String name = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_NAME).getNodeValue();
                String bankAcc = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_BANK_ACC).getNodeValue();
                String description = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_DESC).getNodeValue();
                String objectives = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_OBJ).getNodeValue();
                double iniBudget = 0;
                Date creationDate;
                Date iniDate;
                Date endDate;
                try
                {
                    iniBudget = Double.parseDouble(attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_INI_BUDGET).getNodeValue());
                    String[] creationString = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_CREATION_DATE).getNodeValue().split("-");
                    creationDate =  new Date(Integer.parseInt(creationString[0])-1900, Integer.parseInt(creationString[1]) - 1, Integer.parseInt(creationString[2]));
                    String[] iniString = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_INI_DATE).getNodeValue().split("-");
                    iniDate =  new Date(Integer.parseInt(iniString[0])-1900, Integer.parseInt(iniString[1]) - 1, Integer.parseInt(iniString[2]));
                    String[] endString = attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_END_DATE).getNodeValue().split("-");
                    endDate =  new Date(Integer.parseInt(endString[0])-1900, Integer.parseInt(endString[1]) - 1, Integer.parseInt(endString[2]));
                }
                catch(Exception e)
                {
                    creationDate = new Date();
                    iniDate = new Date();
                    endDate = new Date();
                    //iniBudget = Double.parseDouble(attributes.getNamedItem(Constants.XML_PROJ_HEADER_NODE_INI_BUDGET).getNodeValue());
                }
                NodeList relationNodes = orgNode.getChildNodes();
                HashMap<Integer, OrganizationRelation> relationList = new HashMap<Integer, OrganizationRelation>();
                for(int respIndex = 0; respIndex < relationNodes.getLength(); respIndex++)
                {
                    int userId = Integer.parseInt(relationNodes.item(respIndex).getAttributes().getNamedItem(Constants.XML_PROJ_HEADER_RESP_NODE_ID).getNodeValue());
                    Organization userOrganization = OrganizationManager.GetInstance().GetUserOrganization(userId);
                    if(relationList.containsKey(userOrganization.getId()))
                        relationList.get(userOrganization.getId()).AddResponsible(userOrganization.getResponsible(userId));
                    else
                    {
                        User[] responsibles = {userOrganization.getResponsible(userId)};
                        relationList.put(userOrganization.getId(), new OrganizationRelation(userOrganization, responsibles));
                    }
                }
                projList.add(new Project(projectId, name, creationDate, iniDate, endDate, bankAcc,
                        iniBudget, iniBudget, CommonDataManager.GetInstance().GetCountry(countryId),
                        organization, description, objectives, Enums.ProjectState.incomplete,
                        Enums.ProjectType.Request, new Person[0], null,
                        relationList.values().toArray(new OrganizationRelation[relationList.size()])));
            }
            Project[] projArr = new Project[projList.size()];
            return projList.toArray(projArr);
        }
        catch(Exception ex)
        {
            return null;
        }
    }

    public boolean AddProjecResponsible(int projectId, int userId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = Constants.SP_NAME_ADD_PROJ_RESP;
        SQLParameter[] parameters =
            {new SQLParameter(Constants.SP_PARAM_ADD_PROJ_RESP_PROJ_ID, projectId),
            new SQLParameter(Constants.SP_PARAM_ADD_PROJ_RESP_RESP_ID, userId)};
        try
        {
            accesor.ExecuteOutputSP(spName, parameters);
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }

    public boolean RemoveProjecResponsible(int projectId, int userId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = Constants.SP_NAME_DELETE_PROJ_RESP;
        SQLParameter[] parameters =
            {new SQLParameter(Constants.SP_PARAM_DELETE_PROJ_RESP_PROJ_ID, projectId),
            new SQLParameter(Constants.SP_PARAM_DELETE_PROJ_RESP_RESP_ID, userId)};
        try
        {
            accesor.ExecuteOutputSP(spName, parameters);
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }

    public boolean CreateRequest(
            String name, Date creationDate, Date iniDate, Date endDate,
            String bankAcc, double iniBudget, Country country, String description,
            String objectives, Organization organization, int userId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = Constants.SP_NAME_CREATE_REQUEST;
        SQLParameter[] parameters =
            {new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_NAME, name),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_CREATION_DATE, creationDate),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_INI_DATE, iniDate),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_END_DATE, endDate),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_BANK_ACC, bankAcc),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_INI_BUDGET, iniBudget),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_COUNTRY_ID, country.getId()),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_DESC, description),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_OBJECTIVES, objectives),
            new SQLParameter(Constants.SP_PARAM_CREATE_REQUEST_ORG_ID, organization.getId()),
            new SQLParameter("IdResponsable", userId)};
        try
        {
            ResultSet result = accesor.ExecuteInputSP(spName, parameters);
            return true;
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            return false;
        }
    }

    public void ChangeStatus(int projectId, Enums.ProjectState status)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = "CambiarEstado";
        SQLParameter[] parameters =
            {new SQLParameter("idProyecto", projectId),
            new SQLParameter("estado", EnumConverter.GetEnumName(status))};
        try
        {
            accesor.ExecuteOutputSP(spName, parameters);
        }
        catch(Exception ex){ }
    }

    public void CreateProject(int requestBaseId)
    {
        DataAccesor accesor = DataAccesor.GetInstance();
        String spName = "CrearProyectoDesdeSolicitud";
        SQLParameter[] parameters =
            {new SQLParameter("idSolicitud", requestBaseId)};
        try
        {
            accesor.ExecuteOutputSP(spName, parameters);
        }
        catch(Exception ex){ }
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Private Methods">
    private ProjectSummary[] getProjectSummaries(String spName, SQLParameter[] parameters)
    {
        try
        {
            DataAccesor accesor = DataAccesor.GetInstance();
            ResultSet reader = accesor.ExecuteInputSP(spName, parameters);
            LinkedList<ProjectSummary> result = new LinkedList<ProjectSummary>();
            while(reader.next())
            {
                int id = reader.getInt(Constants.SP_COLUMN_GET_PROJECTS_ID);
                String name = reader.getString(Constants.SP_COLUMN_GET_PROJECTS_NAME);
                Date startDate = reader.getDate(Constants.SP_COLUMN_GET_PROJECTS_INI_DATE);
                Date endDate = reader.getDate(Constants.SP_COLUMN_GET_PROJECTS_END_DATE);
                float budget = reader.getFloat(Constants.SP_COLUMN_GET_PROJECTS_INI_BUDGET);
                String country = reader.getString(Constants.SP_COLUMN_GET_PROJECTS_COUNTRY);
                String organization = reader.getString(Constants.SP_COLUMN_GET_PROJECTS_ORG);
                String statusName = reader.getString(Constants.SP_COLUMN_GET_PROJECTS_STATUS);
                String typeName = reader.getString(Constants.SP_COLUMN_GET_PROJECTS_TYPE);
                Enums.ProjectState status = EnumConverter.GetProjectStateFromString(statusName);
                Enums.ProjectType type = EnumConverter.GetProjectTypeFromString(typeName);
                result.add(new ProjectSummary(id, name, startDate, endDate, budget, country, organization, status, type));
            }
            reader.close();
            ProjectSummary[] arrResult = new ProjectSummary[result.size()];
            return result.toArray(arrResult);
        }
        catch(Exception ex)
        {
            return null;
        }
        
    }

    private int getProjectsCount(String spName, SQLParameter[] parameters)
    {
        try
        {
            DataAccesor accesor = DataAccesor.GetInstance();
            ResultSet reader = accesor.ExecuteInputSP(spName, parameters);
            reader.next();
            int result = reader.getInt(1);
            reader.close();
            return result;
        }
        catch(Exception ex)
        {
            return 0;
        }
    }

    // </editor-fold>
}
